Mechanism to run OLTP workload on in-memory database under memory pressure

ABSTRACT

Techniques are provided for maintaining data persistently in one format, but making that data available to a database server in more than one format. For example, one of the formats in which the data is made available for query processing is based on the on-disk format, while another of the formats in which the data is made available for query processing is independent of the on-disk format. Data that is in the format that is independent of the disk format may be maintained exclusively in volatile memory to reduce the overhead associated with keeping the data in sync with the on-disk format copies of the data.

BENEFIT CLAIM

This application claims the benefit, under 35 U.S.C. § 120 as aContinuation, of prior U.S. patent application Ser. No. 15/005,610titled “Mechanism To Run OLTP Workload On In-Memory Database UnderMemory Pressure” and filed Jan. 25, 2016, which is a Continuation ofprior U.S. patent application Ser. No. 14/337,182 titled “Mechanism ToRun OLTP Workload On In-Memory Database Under Memory Pressure” and filedJul. 21, 2014 (issued as U.S. Pat. No. 9,323,799 B2), the entirecontents each of which are hereby incorporated by reference for allpurposes as if fully set forth herein. Further, U.S. patent applicationSer. No. 14/337,182 claims the benefit under 35 U.S.C. § 119(e) of:

-   -   Provisional Appln. 61/955,574, filed Mar. 19, 2014, and    -   Provisional Appln. 61/880,852, filed Sep. 21, 2013,        the entire contents of each of which is hereby incorporated by        reference for all purposes as if fully set forth herein.

The Applicant(s) hereby rescind any disclaimer of claim scope in theparent application(s) or the prosecution history thereof and advise theUSPTO that the claims in this application may be broader than any claimin the parent application(s).

FIELD OF THE INVENTION

The present invention relates to database systems and, morespecifically, to mirroring, in memory in one format, data that resideson disk in another format.

BACKGROUND

Given that main memory is becoming cheaper and larger, new data formatsare needed to speed query processing when data is stored in memory.Existing formats are designed for disk and, when stored in memory (e.g.in the buffer cache), the formats are not optimal for queries. Forexample, it is common for database systems to store data persistently in“disk blocks”. Typically, within each disk block, data is arranged inrow-major format. That is, the values of all columns of one row arefollowed by the values of all columns for the next row.

To speed up performance, some of the disk blocks may be cached in a“buffer cache” within volatile memory. Accessing the data from volatilememory is significantly faster than accessing the data from disk.However, even within the volatile memory, the data is still in theformat of row-major disk blocks, which is not optimal for certain typesof database operations.

In contrast to row-major disk blocks, columnar formats have manyattractive advantages for query processing in memory, such as cachelocality and compression. Consequently, some database servers now employnew table types for persistently storing data in column-major formats.In column-major format, the data may be read into volatile memory whereit can be used to process certain queries more efficiently than would bepossible if the data were stored in row-major disk blocks.

Unfortunately, the task of migrating existing databases thatpersistently store data in row-major disk blocks to use of the newcolumn-major table types is not trivial. Further, after performing sucha migration, query processing will be less efficient for the class ofqueries that can be performed more efficiently on data that is stored inrow-major disk blocks.

As an alternative, some database systems keep the data in row-major diskblocks, but employ column store indexes. Column store indexes do notreplace existing tables, and therefore do not require the entiredatabase to be migrated to new table structures. Rather, column storeindexes act more as a traditional secondary index. For example, suchcolumn store indexes are still persisted to disk. Unfortunately, asignificant amount of overhead may be required to maintain such indexesas updates are performed on the data indexed thereby.

As yet another alternative, one may replicate a database, where a firstreplica of the database stores the data in conventional row-major diskblocks, while a second replica stores the data in a column-major format.When a database is replicated in this manner, queries that are mostefficiently processed using row-major data may be routed to the firstreplica, and queries that are most efficiently processed usingcolumn-major data may be routed to the second replica.

Unfortunately, this technique does not work well due to the lag thatoccurs between replicated systems. Specifically, at any given point intime, some changes made at one of the replicas will not yet have beenapplied to the other replica. Consequently, the lag inherent in thereplication mechanism may result in unpredictable artifacts and,possibly, incorrect results.

Further, each transaction generally needs to see its own changes, evenbefore those changes have been committed. However, database changes arenot typically replicated until the changes have been committed. Thus, atransaction may be limited to using the replica at which thetransaction's uncommitted changes were made, even though the format ofthe data at the other replica may be more efficient for some operations.

The approaches described in this section are approaches that could bepursued, but not necessarily approaches that have been previouslyconceived or pursued. Therefore, unless otherwise indicated, it shouldnot be assumed that any of the approaches described in this sectionqualify as prior art merely by virtue of their inclusion in thissection.

BRIEF DESCRIPTION OF THE DRAWINGS

In the drawings:

FIG. 1 is a block diagram of a database system that concurrentlymaintains mirror format data in volatile memory and persistent formatdata on persistent storage, according to an embodiment;

FIG. 2a is a block diagram of a table used for examples;

FIG. 2b is a block diagram of how data items for a table may beconcurrently maintained in two formats, one of which is an in-memoryformat, according to an embodiment;

FIG. 3 is a block diagram that illustrates journals stored in volatilememory in conjunction with mirror format data, according to anembodiment;

FIG. 4 is a block diagram illustrating how the data from a single tablemay be divided between IMCUs based on row ranges, according to anembodiment;

FIG. 5a is a block diagram illustrating how different database serverinstances may be assigned to manage different sets of MF data, where thesets are based on row ranges;

FIG. 5b is a block diagram illustrating how different database serverinstances may be assigned to manage different sets of MF data, where thesets are based on columns;

FIG. 6 is a block diagram illustrating an SMU that stores a changed-rowbitmap and a record of bit changes, according to an embodiment;

FIG. 7 is a flowchart illustrating steps for performing a scanoperation, according to an embodiment;

FIG. 8 is a block diagram illustrating a structure for implementing achanged-row bitmap, according to an embodiment; and

FIG. 9 is a block diagram illustrating a computer system that may beused to implement the techniques described herein.

DETAILED DESCRIPTION

In the following description, for the purposes of explanation, numerousspecific details are set forth in order to provide a thoroughunderstanding of the present invention. It will be apparent, however,that the present invention may be practiced without these specificdetails. In other instances, well-known structures and devices are shownin block diagram form in order to avoid unnecessarily obscuring thepresent invention.

General Overview

Different data formats have different benefits. Therefore, techniquesare described herein for maintaining data persistently in one format,but making that data available to a database server in more than oneformat. In one embodiment, one of the formats in which the data is madeavailable for query processing is based on the on-disk format, whileanother of the formats in which the data is made available for queryprocessing is independent of the on-disk format.

The format that corresponds to the on-disk format is referred to hereinas the “persistent format” or “PF”. Data that is in the persistentformat is referred to herein as PF data. An in-memory format that isindependent of the on-disk format is referred to as a “mirror format” or“MF”. Data that is in the mirror format is referred to herein as MFdata. For example, in one embodiment, the persistent format is row-majordisk blocks, and the mirror format is a column-major format.

According to one embodiment, the mirror format is completely independentof the persistent format. However, the MF data is initially constructedin memory based on the persistently stored PF data, not based on anypersistent MF structures. Since persistent MF structures are notrequired, users of existing databases need not migrate the data orstructures in their existing databases to another format. Thus, aconventional database system that uses row-major disk blocks maycontinue to use those disk blocks to persistently store its data withoutperforming any data migration, while still obtaining the performancebenefit that results from having a column-major representation of thedata available in volatile memory.

In-memory MF data is maintained transactionally consistent with the PFdata. The MF data is transactionally consistent in that any data itemsprovided to a transaction from the MF data will be the same version thatwould have been provided if the data items were provided from the PFdata. Further, that version reflects all changes that were committedbefore the snapshot time of the transaction, and no changes that werecommitted after the snapshot time of the transaction. Thus, when atransaction, that made a change to a data item that is mirrored in theMF data, is committed, the change is made visible relative to both thePF data and the MF data. On the other hand, if a transaction that made achange is aborted or rolled back, then the change is rolled backrelative to both the PF data and the MF data.

In one embodiment, the same transaction manager that ensures consistencyamong the reads and writes of the PF data is also used to ensureconsistency among the reads and writes of the MF data. Because the MFdata is kept current in a transactionally consistent manner, if thein-memory MF data includes the data required by a database operation,then the database operation may be satisfied either from the in-memoryMF data, or from the PF data.

The MF data mirrors data that already exists in the PF data. However,while all items in the MF data are mirror versions of correspondingitems in the PF data (albeit organized in a different format), not allitems in the PF data need be mirrored in the MF data. Thus, the MF datamay be a subset of the PF data.

Because not all of the PF data is necessarily mirrored in the MF data,in some situations queries may require data that can only be satisfiedby the PF data. For example, if a table has columns A, B and C, and onlycolumn A is mirrored in the MF data, then a query that requires valuesfrom column B must obtain those values from the PF data.

However, even in those circumstances, the MF data may still be used to(a) satisfy a portion of the query, and/or (b) speed up the retrieval ofrequired data from the PF data. For example, the MF data may be used toidentify the specific rows that must be retrieved from the PF data.

According to one embodiment, to reduce overhead, no on-disk copy of theMF data is maintained. In an alternative embodiment, a copy of the MFmay be stored, but no attempt is made to keep the on-disk copy of the MFdata in sync with updates that are being performed on the PF data.Consequently, after a failure, the in-memory MF data must bereconstructed based on the persistent copy of the PF data.

In some embodiments, the MF data is compressed. The compression can beperformed at various compression levels, either specified by the user orbased on access patterns.

While examples shall be given hereafter in which the mirror format iscolumnar, the mirror format may be any format, different from thepersistent format, that is useful for running in-memory queries. Forexample, in an alternative embodiment, the PF format is column-major,while the MF format is row-major. Regardless of the particular mirrorformat used, the mirror format data is created in memory based onexisting PF structures (e.g. tables and indexes) without causing achange to the format of those structures.

General Architecture

FIG. 1 is a block diagram of a database system according to oneembodiment. Referring to FIG. 1, database system 100 includes volatilememory 102 and persistent storage 110. Volatile memory 102 generallyrepresents the random access memory used by the database system, and maybe implemented by any number of memory devices. Typically, data storedvolatile memory 102 is lost when a failure occurs.

Persistent storage 110 generally represents any number of persistentstorage devices, such as magnetic disks, FLASH memory, and/or solidstate drives. Unlike volatile memory 102, data stored on persistentstorage 110 is not lost when a failure occurs. Consequently, after afailure, the data on persistent storage 110 may be used to rebuild thedata that was lost in volatile memory 102.

Within volatile memory 102, a database server 120 is executing databasecommands that are submitted to the database server by one or moredatabase applications (not shown). The data used by those applicationsis illustrated as PF data 112. PF data 112 resides on persistent storagedevice 110 in PF data structures 108. The PF structures 108 may be, forexample, row-major disk blocks. While row-major disk blocks are used forthe purposes of illustration, the PF structures may take any form, suchas column-major disk block, hybrid compression units, etc.

The volatile memory 102 further includes a cache 106 of PF data. Withincache 106, the data is stored in a format that is based on the format inwhich the data resides within the PF data structures 108. For example,if the persistent format is row-major disk blocks, then cache 106 maycontain cached copies of row-major disk blocks.

On the other hand, MF data 104 is in a format that is unrelated to thepersistent format. For example, in the case where the persistent formatis row-major disk blocks, the mirror format may be column-majorcompression units. Because the mirror format differs from the persistentformat, the MF data 104 is produced by performing transformations on thePF data. These transformations occur both when volatile memory 102 isinitially populated with MF data 104 (whether at start-up or on-demand),and when volatile memory 102 is re-populated with MF data 104 after afailure.

Significantly, the existence of MF data 104 may be transparent to thedatabase applications that submit database commands to the databaseserver that makes use of the MF data 104. For example, those sameapplications, designed to interact with database systems that operateexclusively on PF data 112, may interact without modification with adatabase server that maintains MF data 104 in addition to the PF data112. Further, transparent to those applications, that database servermay use the MF data 104 to more efficiently process some or all of thosedatabase commands.

The Mirror Format Data

MF data 104 may mirror all of the PF data 112, or a subset thereof. Inone embodiment, a user may specify what portion of the PF data 112 is“in-memory enabled”. The specification may be made at any level ofgranularity. For example, the specification of what is in-memory enabledmay be made at least at the following levels of granularity:

-   -   the entire database    -   specified tables    -   specified columns    -   specified row ranges    -   specified partitions    -   specified segments    -   specified extents    -   any combination thereof (e.g. specified columns and partitions)

As shall be described hereafter, in-memory enabled data is converted tothe mirror format and stored as MF data 104 in volatile memory. Thus,when in-memory enabled data is required by a query, the database serverhas the option of providing the data from either the PF data 112 or theMF data 104. The conversion and loading may occur at the time thedatabase is started, or in a lazy or on-demand fashion. Data that is notin-memory enabled is not mirrored in the MF data 104. Consequently, whensuch data is required by a query, the database server does not have theoption of obtaining the data from the MF data 104.

For the purpose of explanation, it shall be assumed that PF datastructures 108 include the table 200 illustrated in FIG. 2A. Table 200includes three columns c1-c3, and six rows r1-r6. While the illustrationof table 200 in FIG. 2A portrays how the data is logically organized onpersistent storage 110, the actual format in which the data isphysically stored may be quite different.

Specifically, referring to FIG. 2B, it illustrates how the data thatresides in table 200 may be physically organized on persistent storage110. In the present example, the data for table 200 is stored in threerow-major disk blocks 202, 204 and 206. Block 202 stores the values forall columns of row r1, followed by the values for all columns of row r2.Block 204 stores the values for all columns of row r3, followed by thevalues of all columns of row r4. Finally, block 206 stores the values ofall columns of row r5, followed by the values of all columns of row r6.

Copies of some of those disk blocks may be temporarily stored in cache106. In the example illustrated in FIG. 2B, a cached copy 212 of block204 resides in cache 106. Cache 106 may be managed using any one of avariety of cache management techniques, and the embodiments describedherein are not limited to any particular cache management technique. Ingeneral, such techniques attempt to retain in volatile memory 102 copiesof the disk blocks that are most likely to be requested in the nearfuture. Consequently, when cache 106 runs out of space, cached copies ofdisk blocks that are less likely to be requested are replaced by copiesof blocks that are more likely to be requested.

In contrast to the data in cache 106, the mirror format data 104 is notformatted in a manner that is based on the persistent format. In theillustrated example, mirror format data 104 includes two column vectors220 and 222. Each column vector stores a contiguous series of valuesfrom a single column of table 200. In the present example, column vector220 stores values from column 1 of table 200, and column vector 222stores values from column 3 of table 300. In this example, the MF data104 mirrors a subset of the PF data because MF data 104 does not includecolumn vectors for column 2 of table 200.

Organization of the MF Data

According to one embodiment, even though the MF data uses a differentformat than the PF data, the MF data is organized in a manner thatcorresponds to the organization of the PF data. For example, onpersistent storage 110, the PF data may be stored in blocks that residein extents which, in turn, are organized into segments. Under thesecircumstances, within volatile memory 102, the MF data 104 may beorganized based on the extents and/or segments to which the databelongs. Thus, column vector 220 may be divided into vector portions,each of which corresponds to a particular range of extents and/orsegments.

Within the extents, data is typically ordered by rowid. Similarly, inone embodiment, the MF data 104 is ordered based on rowid. For example,the values in column vector 220 are ordered based on the same rowidsthat are used to order the PF data in blocks 202, 204 and 206.Specifically, rowid r1 immediately precedes rowid r2, so r1 c 1immediately precedes r2 c 1 in column vector 220, and r1 c 1 to r1 c 3immediately precede r2 c 1 to r2 c 3 in block 202.

In alternative embodiments, some or all of the data items in the MF data104 are not ordered, within the MF data 104, by rowid. Storing the dataitems in a different order may be useful, for example, if the differentordering produced significantly better compression. As another example,the column vectors may initially be ordered by rowid. However, when newupdates are “merged into” the column vectors (as shall be discussed ingreater detail hereafter), the updated values may appended to the end ofthe existing column vectors to avoid having to decompress and recompressthe existing column vectors.

When the data items within the column vectors are not in rowid order, anin-memory index may be built on rowid to quickly locate within the MFdata 104 the data items associated with any given rowid.

Whether or not the data items within the column row vectors are orderedbased on rowid, a rowid-to-item mapping may be established bymaintaining a vector of rowids in conjunction with the column vectors.For example, FIG. 3 illustrates a rowid vector 330 that is maintained inaddition to the column vectors 220 and 222. The first value (R1) in thevector of rowids is the rowid of the first data item in each of thecolumn vectors. Similarly, the second value in the vector of rowids (R2)is the rowid of the second data item in each of the column vectors.

In embodiments where the organization of the MF data corresponds to theorganization of the PF data, it is easier for the database server tosplit database operations between the MF data and the PF data. Forexample, the database server may determine that the MF data is to beused to satisfy a query relative to one range of extents (e.g. extent 1to extent 10), while PF data is to be used to satisfy the query relativeto another range of extents (e.g. extent 11 to extent 20).

Using the MF Data to Satisfy Queries

A conventional database system may operate normally by responding toevery query by first searching for the requested data in cache 106. Ifthe data is in cache 106, the data is accessed from cache 106.Otherwise, the needed data is loaded from PF data structures 108 intocache 106, and then accessed from cache 106. However, because the datain both cache 106 and PF data structures 108 is in the persistentformat, performing operations based exclusively on the PF data does notalways provide the best performance.

Therefore, according to one embodiment, the database server uses the MFdata 104 to supply data items required by at least some requesteddatabase operations. For example, if a database query requests thevalues from column 1 of all rows, the database server may obtain thosevalues from the column vector 220 without accessing persistent storage110. In the absence of MF data 104, the database would only be able toobtain R3C1 and R4C1 without accessing persistent storage 110 (becausecurrently only block 204 is in cache 106). To obtain R1C1 and R2C1,block 202 must be loaded into cache 106, and to obtain R5C1 and R6C1,block 206 must be loaded into cache 106. The time it would take to loadblocks 202 and 206 into cache would be significantly more than the timerequired to obtain the values directly from column vector 220.

Using the MF Data to Evaluate Predicates

Even in situations where the data required by a database operation isnot included in the mirror format data 104, the mirror format data 104may be used to evaluate predicates, and thereby speed up the databaseoperations in the same manner as conventional indexes. For example,assume that table 200 has thousands of rows, and in only three of thoserows does column c1 have the value “joe”. Under these circumstances, adatabase server may receive a database command that requests the values,from column c2, of all rows where c1=“joe”.

In this example, the data that needs to be returned by the databasecommand is from column c2, which is not in the MF data 104. However, thecolumn vector 220 for column 1 may be used to quickly identify the threerows where c1=“joe”. This operation can be performed efficiently becausethe data items required to evaluate the predicate (values from c1) arestored contiguously in volatile memory. Once those rows have beenidentified using column vector 220, the database server may retrievefrom disk only those blocks needed to obtain the data from those threerows.

Without using the MF data, a conventional index built on column c1 maybe used to evaluate the predicate “where c1=joe”. However, some disk I/Omay be necessary to make use of the conventional index to perform thisevaluation, whereas no disk I/O is needed to evaluate the predicateusing column vector 220. Further, maintaining such an index can incursignificant overhead.

Without using mirror format data 104 or a conventional index, thedatabase server would have to load from persistent storage 110 everydisk block that (a) is not already in cache 106, and (b) stores data fortable 200. These blocks would have to be loaded merely to compare thevalues of column c1 against “joe” to identify the three rows for whichc2 is required by the database command.

Because MF data 104 may be used for the same function as conventionalindexes (i.e. to efficiently identify which rows satisfy criteriaspecified in a database command), a database system that uses MF data104 need not have as many conventional indexes as would otherwise benecessary for efficient predicate evaluation. For example, if the MFdata 104 includes a column vector for c1 and a column vector for c3,then the database server need not maintain conventional indexes forcolumns c1 or c3. By reducing the number of conventional indexes thatneed to be maintained by a database server, the overhead associated withmaking updates may be significantly reduced.

In-Memory Indexes

As explained above, when a predicate references a column, the columnvector for that column may be used to evaluate the predicate. In thisway, column vectors may be used instead of conventional indexes. Toprovide even faster predicate evaluation, in-memory indexes may be used.An in-memory index is an index stored entirely within volatile memory.The nature of the in-memory index may vary based on the characteristicsof the data being indexed. For example, if low-cardinality keys arebeing indexed, the in-memory index may be a binary index. Ifhigh-cardinality keys are being indexed, then the in-memory index may bea B-tree. Regardless of the nature of the in-memory index, the entriesin the index point to in-memory location of the data items in question,rather than on-disk locations.

Compression

As mentioned above, the MF data may be compressed. However, according toone embodiment, not all MF data need be compressed in the same way, orto the same degree. For example, if it is determined that the data fromcolumn c1 of table 200 is used frequently, and the data from column c3is used infrequently, then the data in column vector 220 may be lightlycompressed, or uncompressed, whereas the data in column vector 222 ishighly compressed.

The compression algorithm, and the level of compression used by thealgorithm, that is used to compress each portion of the MF data may bespecified by a user, or may be determined automatically by a databaseserver based on various factors. Possible compression algorithmsinclude, but are not limited to, dictionary-based compression,run-length encoding (RLE), Ozip compression, etc. Ozip compress isdescribed in U.S. Provisional Patent No. 61/955,574, filed Mar. 19,2014, the contents of which are incorporated herein by this reference.

The factors used by the database server to determine how each portion ofMF data is compressed may include, for example, the frequency with whicheach portion is accessed, and how much data is in the portion, and howmuch volatile memory is available. In general, the more frequently aportion of the MF data is accessed, the less compressed the data. Asanother general rule, the less volatile memory that is available tostore the MF data and/or the larger the size of the portion of the MFdata, the higher the compression.

Even though data items may be compressed within the MF data, it may notbe necessary to decompress the MF data to use it. For example, vectorprocessing operations may be performed directly on compressed values, asdescribed in U.S. patent application Ser. No. 13/708,054, filed Dec. 7,2012, the entire contents of which are incorporated herein by reference.As also described in that application, it is also possible for thedecompression to be performed on-chip after the compressed column vectorvalues have been transferred to the CPU.

In-Memory Compression Units (IMCUs)

In an embodiment in which the MF data is compressed, the MF data may beorganized, within volatile memory 102, into “in-memory compressionunits” (IMCUs). Each IMCU stores a different set of MF data. Forexample, as illustrated in FIG. 4, IMCU 402 stores half of columnvectors 220 and 222, and IMCU 404 stores the other half of columnvectors 220 and 222. Specifically, IMCU 402 includes a vector portion420 that stores half the values from column c1, and a vector portion 422that stores half the values from column c3. Similarly, IMCU 404 includesa vector portion 424 that stores the other half of the values fromcolumn c1, and a vector portion 426 that stores the other half thevalues from column c3.

In this example, the IMCUs divide the MF data based on the rows to whichthe data belongs, where IMCU 402 corresponds to rows r1 to r3 of table200, and IMCU 404 corresponds to rows r4-r6 of table 200. However, thisis only one of many different ways that the MF data may be spread amongIMCUs. For example, different IMCUs may store MF data for differenttables, different partitions of a table, different columns of a table,different segments, different extents, etc.

Metadata for the MF Data

To determine whether the MF data has the data required to process aquery, and if so, to find the MF data required to process the query, thedatabase server needs to know which PF data is mirrored in the MF data,and specifically which specific PF data is mirrored by each IMCU.Therefore, according to one embodiment, metadata 430 for the MF data ismaintained in volatile memory 102, as illustrated in FIG. 4.

In one embodiment, metadata 430 includes a data-to-IMCU mapping. Thedata-to-IMCU mapping indicates which data is contained in each IMCU.This indication may be made in a variety of ways, including storing datathat indicates, for each IMCU, one or more of the following:

-   -   the table(s) whose data is stored in the IMCU    -   the column(s) whose data is stored in the IMCU    -   the range of rows stored in the IMCU    -   the range of the disk blocks whose data is stored in the IMCU    -   the segments whose data is stored in the IMCU    -   the table partitions whose data is stored in the IMCU    -   the extents whose data is stored in the IMCU    -   the manner in which the data, within the IMCU, has been        compressed    -   the dictionary for decompressing the data stored in the IMCU        (when a dictionary-type encoding has been used to compress the        PF data)

In the situation illustrated in FIG. 4, the data-to-IMCU mapping mayindicate, for example, that rows r1-r3 of columns c1 and c3 of table 200are stored in IMCU 402, and that rows r4-r6 of columns c1 and c3 oftable 200 are stored in IMCU 404.

Multi-Instance Environments

In some environments, the same PF data is accessed by multiple databaseserver instances. Such environments are referred to herein asmulti-instance environments. In a multi-instance environment, eachdatabase server instance may have access to volatile memory that theother database server instances cannot access directly. In suchsituations, one may populate the volatile memory of each of the databaseserver instances with the same MF data, or may cause different portionsof the MF data to be stored in the volatile memories of differentdatabase server instances. In the case where different portions of theMF data is stored in the volatile memories of different database serverinstances, metadata 430 may also include an IMCU-to-instance mapping.

For example, referring to FIG. 5a , it illustrates an embodiment inwhich IMCU 402 is stored in the volatile memory 502 of one databaseserver instance (instance 1), and IMCU 404 is stored in the volatilememory 504 of another database server instance (instance 2). For thedatabase servers to know where specific portions of the MF data reside,each maintains metadata (530 and 532) to indicate both (a) where IMCUs402 and 404 reside, and (b) what data they contain.

In FIG. 5a , MF data from the same two columns (c1 and c3) isdistributed between two database instances. However, it is also possibleto distribute the MF data among database servers on other basis. Forexample, different instances may have the MF data for different tables,different columns, different partitions, different segments, differentextents, etc.

FIG. 5b is a block diagram of a scenario in which the MF data isdistributed among the database instances based on column. Specifically,in FIG. 5b , the IMCU 402 stored in the volatile memory 502 of instance1 includes the entire column vector 220 for column c1, while the IMCU404 stored in the volatile memory 504 of instance 2 includes the entirecolumn vector 222 for column c3.

Because it is more efficient to access local data than to obtain datafrom a remote instance, the location of the MF data may be a factor indetermining whether to obtain a particular data item from the MF data orthe PF data. For example, in the scenario illustrated in FIG. 5b , if aquery that is being executed by the database server of instance 1requires data from column c1, the database server may decide to obtainthe data from column vector 220, rather than from the PF data. On theother hand, if the same query being executed by the same database serverrequires data from column c3, then the database server may decide toobtain the data from the PF data.

When a database server determines that it is more efficient to performan operation using MF data that resides in a remote instance that to usethe PF data, the database server requests the remote instance to performthe operation. For example, in the scenario illustrated in FIG. 5b , ifthe database server of instance 1 is executing a query with thepredicate “where c3=X”, the database server of instance 1 would requestthe database server of instance 2 to evaluate “where c3=X” using columnvector 222. In response to evaluating the predicate, the database serverof instance 2 would return to the database server of instance 1 datathat indicates which rows satisfy the predicate.

Keeping the Mirror Format Data in Sync

The MF data 104 is only useful if the MF data 104 is kept up to datewith all changes being made to the PF data. For example, if a querycalls for the current values from column c1, then column vector 220 canonly be used if its values are current. Similarly, if a query calls forcurrent values of c2 from rows where c1=“joe”, then column vector 220can only be used to identify the rows where c1=“joe” if the values inthe column vector 220 are current.

Consequently, a mechanism is provided for keeping the mirror format data104 in sync with the PF data as updates, inserts and deletes areperformed on the PF data. Specifically, in one embodiment, thetransaction manager of a relational database server, which isconventionally designed to transactionally update the PF data, ismodified to concurrently transactionally update the MF data. Forexample, when the transaction manager updates a particular item in thePF data as part of a transaction, the transaction manager also updatesthe particular item in the MF data (if the particular item is in the MFdata) as part of the same transaction.

By maintaining MF data 104 and the PF data transactionally synchronized,the result set of a query will be the same regardless of whether thequery was processed using data items obtained exclusively from the MFdata 104, or data items obtained exclusively from the PF data. Theresult set will also be the same if the query is processed using somedata items from MF data 104, and other data items from the PF data.

In-Place Updates to MF Data

For the MF data to remain transactionally consistent with the PF data,changes are made permanent to the MF data at the same time the changesare made permanent to the PF data. For example, when a transaction thatchanged r1 c 1 from X to Y commits, r1 c 1 must be changed from X to Yin both the PF data and the MF data.

In some situations, it is possible to directly update the MF data toreflect a change made by a transaction when the transaction commits. Forexample, if column vector 220 is either uncompressed, or compressed in amanner that produces fixed-width values, it is possible to directlychange the value of r1 c 1 from X to Y in the column vector 220 when thetransaction commits, without otherwise affecting the column vector 220or incurring significant overhead.

However, in other situations, it may be necessary to update the MF dataimplicitly. When updated implicitly, the MF data itself does notnecessarily change, but metadata is stored to indicate that the valuescontained therein have been updated. As shall be described in greaterdetail hereafter, the metadata used to record implicit updates to the MFdata may include journals and changed-row bitmaps.

Journals

In some embodiments, keeping the MF data in sync with updates to the PFdata is complicated by the fact that the MF data may be in a compressedformat. For example, if column vector 220 is compressed, then directlyupdating a value within the column vector 220 may require the entirecolumn vector to be decompressed, the update performed, and then theentire column vector to be compressed again. It would not be efficientto perform such operations in response to every update performed on thePF data.

To reduce the amount of decompression and decompression operationsrequired to keep the MF data in sync, one embodiment makes use ofjournals to make implicit updates to the MF data. In general, journalsstore information about updates (a) made to the PF data, and (b) not yetmade directly to the MF data.

Referring to FIG. 3, it illustrates an embodiment in which journals 304are maintained in conjunction with column vectors 220 and 222. In theembodiment illustrated in FIG. 3, column vectors 220 and 222 storecompressed MF data 302. Because the data within the column vectors 220and 222 is compressed, a significant amount of overhead would berequired to directly update the data within the column vectors 220 and222.

While journals 304 are also in volatile memory 102, journals 304generally contain uncompressed data 302 that indicates changes made tothe PF data that is not yet reflected in column vectors 220 and 222. Forexample, if the value of R3C1 of table 200 is updated from X to Y,rather than change the value of R3C1 in column vector 220, an entry isstored in one of journals 304 to indicate that R3C1 has been changed,and to record the new value for R3C1.

Journals 310 include a global journal 310 and numerous private journals.In general, global journal 310 records only those changes that have beenmade by committed transactions. Before transactions commit, the changesmade by the transactions are stored in the private journals, asexplained in greater detail hereafter.

Journals 310 may include entries for rows that do not exist in the MFdata. For example, assume that the MF data for table 200 is created attime T1, and at time T2 a new row is inserted into table 200. Underthese circumstances, an entry for the new row will initially be added toprivate journal of the transaction that inserted the row, and the entryfor the new row will be moved to the global journal for table 200 whenthat transaction commits.

According to one embodiment, all journals support full transactionalsemantics (e.g. queries, DMLs, rollback to savepoint, rollback/abort,parallel queries/DMLs, and distributed transactions). In addition,journals can interoperate with the on-disk database system. For example,when the data is purged from an in-memory journal, required changes canbe obtained from the PF data on disk, if a query needs them.

Private Journals

As mentioned above, journals 304 are used to store data that indicates(a) changes made to the PF data that (b) are not yet reflected in the MFdata stored in IMCUs. Such changes are typically made by databaseservers as part of transactions. According to one embodiment, inaddition to having a single “global” journal, such as journal 310, forall such changes, a separate “private” journal is maintained for eachtransaction.

For example, FIG. 3 illustrates the situation in which threetransactions TX1, TX2 and TX3 are making changes to PF data that ismirrored in the compressed MF data 302. In addition to making thechanges to the PF data, the transactions make the same changes to the MFdata by storing, in their respective private journals, data thatindicates what the changes are.

Similar to the changes made to the PF data, those changes reflected inthe private journal of a transaction are not considered permanent untilthe transaction commits. Consequently, the changes reflected in theprivate journal of any given transaction will not be visible to othertransactions until the given transaction commits. In the example shownin FIG. 3, the contents of journal 312 will be ignored by transactionsTX2 and TX3. The contents of journal 314 will be ignored by transactionsTX1 and TX3. The contents of journal 316 will be ignored by transactionsTX1 and TX2.

Moving Journal Entries Upon Commit

The global journal is visible system-wide, since all changes reflectedtherein have been committed. Thus, in response to transaction TX1committing, the changes reflected in the private journal 312 of TX1 aremoved to the global journal 130. Similarly, in response to transactionTX2 committing, the changes reflected in the private journal 314 of TX2are moved to the global journal 130. Likewise, in response totransaction TX3 committing, the changes reflected in the private journal316 of TX6 are moved to the global journal 130.

As mentioned above, when a transaction commits, the contents of thattransaction's private journal are moved to the appropriate globaljournals. In embodiments where the global journals are maintained on aper-IMCU basis and the private journals are maintained on aper-transaction basis, moving the private journal entries of a committedtransaction may involve moving some of the entries to the global journalof one IMCU, and some entries to the global journal of another IMCU.

For example, assume that a transaction modifies a first set of data thatmaps to a first IMCU, and modifies a second set of data that maps to asecond IMCU. Prior to commit, entries for both sets of modifications arestored in the private journal of the transaction. However, when thetransaction commits, the entries for modifications to the first set ofdata are moved to the global journal for the first IMCU, and entries formodifications to the second set of data are moved to the global journalfor the second IMCU.

After a transaction's changes are persistently committed to the PF data,the transaction is assigned a commit time. In response to being assigneda commit time, the journal entries of the transaction are updated toreflect the commit time. Once a transaction's journal entries are movedto the appropriate global journals and updated with the commit time ofthe transaction, the changes reflected in those entries become visibleto other transactions.

As mentioned above, data within an IMCU need not be arranged in rowidorder. When not in rowid order, the column vector of rowids (e.g. vector330) may be used to locate data within an IMCU based on rowid.Specifically, the position of a rowid within vector 330 is the positionof the values for the corresponding row within the other vectors 220 and222. According to one embodiment, even when the data within an IMCU isnot arranged in rowid order, the entries in the corresponding privateand global journals are organized based on rowid. Thus, when data in anIMCU is invalidated due to an update made to the corresponding PF data,the rowid of the invalidated data is recorded, rather than the positionof that data within the IMCU.

Journal Entry Contents

In general, each journal entry contains all information required todetermine (a) what data items are in the entry, and (b) what version ofthose data items does the entry reflect. In one embodiment, each journalentry includes:

-   -   the rowid of the row associated with the entry    -   a timestamp that indicates when the data contained in the row        was “current”    -   values for one or more columns of the corresponding row

With respect to the column values, in one embodiment, each journal entryincludes full row images that result from all Data Manipulation Language(DML) operations. In such an embodiment, the journals are initiallyrow-major data stores. However, under certain circumstances (such aswhen the journals grow too large), the contents of the journal may beconverted to a column-major row store. The column-major information inthe journals would only need to include values for those columns thatare mirrored in the MF data.

In on embodiment, a threshold is established for how many rows a journalmay have in row-major format. Once that threshold is exceeded, aconversion operation is triggered for converting some or all of thejournal's row-major data to a column-major format. The threshold may be,for example, that a journal may have no more than 1000 rows of row-majordata.

Journal Indexes

According to one embodiment, an index, maintained in volatile memory102, is built on the rowid column of each private journal. In additionto the rowid column, indexes may be built on any other column of theprivate journals that will improve overall query processing efficiency.These journal indexes may be used, for example, during query processingto perform look-ups, or range-based scans of the journals.

Journal Structure

According to an embodiment, journals are organized, within volatilememory 102, as a series of temporally-ordered extents. For example,assume that the version time for MF data 104 is T1, and that the currentsystem time is time T10. Under these circumstances, journal 310 may beorganized into three extents, the first of which includes journalentries for changes made between time T1 and time T3, the second ofwhich includes journal entries for changes made between time T3 and timeT6, and the third of which includes journal entries for changes madebetween time T6 and the current system time.

When structured in this manner, extent pruning may be used to reduce thenumber of extents that are processed during a table scan. For example,for a table scan performed for a transaction with a snapshot time of T2,only the first extent of journal 310 would need to be scanned. The otherjournals contain only changes that the transaction is not allowed tosee.

On the other hand, for a table scan performed for a transaction with asnapshot time of T7, all three extents of journal 310 would have to bescanned, because all three could contain journal entries for changesthat must be seen by the transaction.

Merging Global Journals into the MF Data

As mentioned above, journals are used because it is inefficient toupdate the MF data directly every time a database operation makes achange to the corresponding PF data. This is particularly true when theMF data is compressed. However, it is also inefficient to allow thejournals to grow indefinitely, both because eventually the journals willrequire too much volatile memory, and because the larger the journalsgrow, the less efficient it becomes to use the MF data to satisfyqueries.

Consequently, according to one embodiment, the content of the globaljournals is periodically merged into the MF data. When the MF data iscompressed, this merger operation typically involves decompressing theMF data, updating the MF data to reflect the most current committedversions of the items contained therein, and then compressing the MFdata.

After data has been merged into the MF data contained in a particularIMCU, the metadata associated with the IMCU is updated to indicate a newversion timestamp for the IMCU. For example, if the MF data in an IMCUreflected all changes made as of time T1, then prior to the merger theversion timestamp for the IMCU would be T1. If the update involvesmerging into the MF data of the IMCU all changes that were made up totime T3, then after the merger the version timestamp for the IMCU wouldbe updated to T3.

Post-Merger Retention of Global Journal Entries

As shall be described in greater detail hereafter, in some embodiments,changed-row bitmaps may be used to indicate which data items in the MFdata have become stale. A data item in the MF data becomes stale when achange (not reflected in the MF data) is committed to the data item.Once the contents of a global journal have been merged into thecorresponding MF data, the old entries in the journals may be purged andthe changed-row bitmap is updated to reset all bits (thereby indicatingthat no data items in the newly-merged MF data are stale). However, insome embodiments, rather than purge all old journal entries in responseto merging changes into the MF data, some of the old data may beretained in order to continue to use the MF data for transactions whosesnapshot-times are before the merger time.

For example, if the post-merger version timestamp for the IMCU is T3,then a transaction with a snapshot time of T2 cannot use the MF data inthe IMCU, because that data contains changes that the transaction is notallowed to see. However, if all the journal entries as of time T1 havebeen retained, it is possible to use those journal entries, inconjunction with the IMCU, to obtain some data items as of time T2.Specifically, for a data item whose journal entries have been retained,a transaction with a snapshot time of T2 would use the version of thedata item from the most recent journal entry that precedes T2, thesnapshot time of the transaction.

For example, assume that the journal only has a single entry, and theentry indicates that r5 c 1 was changed at time T3 from X to Y.Consequently, the post-merger IMCU will have value Y for r5 c 1.However, to provide the correct value to the transaction, the databaseserver inspects the journal to see that the row of r5 c 1 was changedbetween the snapshot time T2 and the version time T3 of the IMCU. Basedon this information, the database server knows that the value Y for r5 c1 is too recent for the transaction to see, and that the transactionmust instead see value X for r5 c 1. Consequently, in the data obtainedfor the transaction, the database server changes the value of r5 c 1 Yto X.

Unfortunately, it is not feasible to retain old journal entriesindefinitely. Therefore, according to one embodiment, a configurationparameter is provided for specifying the retention policies associatedwith IMCUs or the database objects to which they correspond. Forexample, a retention policy may be that, for table 200, journal entriesare retained for at least one hour. Thus, for IMCUs that contain datafor table 200, when purging journal entries after a merger, only thosejournal entries that are associated with snapshot times that are lessthan an hour old are retained. Retaining already-merged journal entriesin this manner ensures that transactions that have snapshot times lessthan an hour old will always be able to obtain the correct version ofdata items from the MF data.

According to one embodiment, old journal entries are retained until thedatabase server determines that no currently executing queries will needthe old journal entries. For example, if changes are merged into an IMCUat time T10, then journal entries, in the global journal of that IMCU,that are associated with changes made before time T10 may automaticallybe purged by the database server when there are no morecurrently-running transactions that have snapshot times before T10.

In some embodiments, the journal entries may store only an indication ofwhich row was changed, and when, without storing the actual valuesinvolved. In such an embodiment, the pre-merger journal entries arestill useful for indicating which values from the post-merger IMCUcannot be used by a transaction. In the example given above, the versionof r5 c 1 that is in the post-merger IMCU cannot be used for atransaction with a snapshot time of T2, because the journal wouldindicate that r5 c 1 was changed between the snapshot time T2 and theversion time T3 of the post-merger IMCU. Under these circumstances, ifthe journal does not have the actual pre-update value of r5 c 1 (i.e.X), the database server may obtain that value from the PF data, and therest of the values that it needs from the MF data.

Global Journals and Memory Constraints

As explained above, both global and private journals are maintained involatile memory. Private journals are used to record changes made bytransactions that have not yet committed. Global journals, on the otherhand, generally record changes made by transactions that have committed.

The more entries that a global journal has, the more volatile memory isconsumed. Under some circumstances, there may simply not be enoughvolatile memory to store excessively large global journals. One way ofhandling these situations is to purge older extents of the journals.

For example, assume that the global journal of an IMCU has three extentsE1, E2 and E3. Assume further that E1 contains entries for transactionsthat committed between time T1 and time T5, E2 contains entries fortransactions that committed between time T5 and time T9, and E3 hasjournal entries for transactions that committed between time T9 and thecurrent system time.

Assume further that the version time of the IMCU is T5. Under thesecircumstances, the entries in E1 may be used to “roll back” the valuesin the IMCU for transactions that have snapshot times between T1 and T5.On the other hand, the entries in E2 and E3 may be used to “rollforward” the values in the IMCU for transactions that have snapshottimes after T5.

When faced with memory constraints, the database server may purgeextents E1 only, E1 and E3, or E1, E2 and E3, depending on how muchmemory is needed. Purging an extent has an effect on performance ofcertain transactions. For example, assume that E1 is purged. After E1 ispurged, a transaction with a snapshot time of T3 may require data itemsthat maps to the IMCU. The transaction may obtain data items that didnot change between T3 and T5 from the IMCU. Data items that did changebetween T3 and T5 are obtained from the PF data, because those itemswere recorded in E1 which has been purged.

Even after the purging of its journals, an IMCU may be used to supplydata that did not change between (a) the version time of the IMCU and(b) the snapshot time of the transaction requesting the data. Forexample, if the IMCU version time is T1, a transaction with a snapshottime of T5 may obtain data items from the IMCU that were not changedbetween T1 and T5. As shall be described in greater detail hereafter,those changed data items may be identified using a delete vectorgenerated for the transaction.

Snapshot Metadata Units

As mentioned above, metadata is maintained for each IMCU. In oneembodiment, a Snapshot Metadata Unit (SMU) is responsible formaintaining at least some of that metadata. Referring to FIG. 6, an IMCU600 is illustrated with its corresponding SMU 604. In the illustratedembodiment, SMU 604 stores the IMCU version time and a changed-rowbitmap 606. The IMCU version time is the time at which the values in theIMCU 600 were current. Changed-row bitmaps shall be described in greaterdetail hereafter.

Among other things, the SMU for an IMCU captures all updates that affectthe MF data contained in the IMCU. Consequently, the SMU for an IMCU mayindicate, for example, whether the corresponding IMCU has valid valuesfor a given rowid/snapshot-time combination. As another example, the SMUmay generate a list of rowids of all rows for which the correspondingIMCU has invalid values, relative to a given snapshot-time. This listmay then be used in conjunction with the rowid column vector to identifythe rows for which values must be obtained from other sources (e.g. fromjournals or from the PF data).

Changed-Row Bitmaps

In one embodiment, the updates captured by an SMU are indicated by a“changed-row bitmap” maintained within the SMU. Referring again to FIG.6, the changed row bitmap 606 for IMCU 600 is maintained in SMU 604. Achanged-row bitmap is a bitmap that indicates the rows (a) for which thecorresponding IMCU has values, and (b) that have been changed bytransactions that committed since the version timestamp of the IMCU.

For example, when a transaction performs an update to rows r1, r3 and r5of table 200, the SMU 604 for IMCU 600 updates the changed-row bitmap ofIMCU 600 by setting the bits that correspond to rows r1, r3, and r5because those are the updated rows that fall within the MF data of IMCU600.

According to one embodiment, when a change is made to data that ismirrored in IMCU 600, the SMU 604 stores a record of which bits of thechanged-row bitmap 606 were set, and when. These records arecollectively represented in FIG. 6 as record of bit changes 608. Forexample, if an update made at time T1 modifies row r1, then the bit forrow r1 would be set, and a record is stored to indicate that the bit forr1 was set at time T1.

According to one embodiment, the changed-row bitmap is created on anas-needed basis. For example, if the changed-row bitmap is to reflectwhether a change has occurred to a million rows, a one million bit datastructure is not pro-actively initialized. Instead, data is only storedfor row ranges that have at least one bit set. For any range for whichno data is stored, all bits are deemed to be “0”.

Referring to FIG. 8, it illustrates a hierarchical structure 800 forrepresenting a changed-row bitmap, according to one embodiment. In theillustrated embodiment, the hierarchical structure 800 has levels thatcorrespond to extents, blocks, and rows. The extent-level information802 includes a record for each extent in which there is any set bit.Extent-level records link to other extent-level records (not shown),thereby forming a linked list of records for the extents that have oneor more set bits.

In addition, the extent records include a pointer to a linked list ofblock level information 804 for the blocks that reside in the extent. Inthe illustrated example, the record for extent E1 points to the recordfor blocks B1, B2, B3 and B4. The block-level record can be the firstrecord in a linked list of block-level records for the blocks thatbelong to extent E1.

The block-level records, in turn, point to row-level information 806stored in the form of bitmap pieces. Specifically, in the illustratedembodiment, the record for block B1 points to bitmap piece 850.

Each position in the bitmap piece 850 corresponds to a row whose dataitems are stored in block B1. In the illustrated embodiment, bitmappiece 850 has six bit positions, which correspond to six rows are storedin B1. For each bit position, bitmap piece 850 includes two bits, one ofwhich is a row-changed bit 820 and the other of which is an in-journalbit 830. For any given row, the row-changed bit indicates that the rowchanged since data items for the row were stored in the IMCU. Thein-journal bit for a row indicates whether the updated values for therow are stored in the IMCU's journal.

Based on the information in data structure 800, the database server maydetermine whether the current version of a data item resides in theIMCU, in the journals of the IMCU, or in neither. Specifically, if thestructure 800 has no information for a given row, then the IMCU has thecurrent version of the data items from the row. The IMCU also has thecurrent version of the data items from the row if the structure 800 hasinformation for the row, and the row-changed bit for the row is “0”. Ifstructure 800 has information for the row, the row-changed bit is setand the in-journal bit is set, then the IMCU does not have the currentversion of the item, but the journal for the IMCU does have the currentversion of the item. Finally, if structure 800 has information for therow, the row-changed bit is set, and the in-journal bit is not set, thenneither the IMCU nor the journals have the current version of the dataitem, and the current version must be retrieved from the PF data.

The records of structure 800 are created on an as-needed basis. Thus, ifthe IMCU is current for all data items in a particular extent, thenstructure 800 may not have any records for the extent. Similarly, if theIMCU is current for all data items in a particular block, then structure800 may not have any block level information 804 for that block. By onlystoring changed-row information for extents/blocks that have beenchanged or added since the version time of the IMCU, structure 800 maybe significantly small than it would otherwise be if bits werepre-allocated for every row.

Using the Record of Bit Changes

For a transaction that requires the most recent version of data items, aset bit in the changed-row bitmap 606 indicates that the MF data hasstale data for that row, and therefore the IMCU 600 cannot be used tosupply data from that row. However, not all transactions require themost recent version of data items.

For example, in many database systems, transactions are assigned asnapshot time, and return data that reflects the state of the databaseas of that snapshot time. Specifically, if a transaction is assigned asnapshot time of T3, then the transaction must be provided versions ofdata items that include all changes that were committed before T3, andno changes that were not committed as of T3 (except for changes that thetransaction makes itself). For such transactions, a set bit in thechanged-row bitmap 606 does not necessarily indicate that the IMCU 600cannot be used to be the source for items for the corresponding row.Specifically, such transactions may still use the IMCU 600 to obtaindata for a particular row, even though the bit for that row is set inchanged-row bitmap 606, if the bit was first set after the snapshot timeof the transaction.

For example, assume that the column vectors 220 and 222 contain data asit existed at time T1, as indicated by the IMCU version time stored inSMU 604. At a later time T5, an update operation changes row r1.Specifically, the update changes the value of r1 c 1 for X to Y. Inresponse to this update, the changed-row bitmap 606 of IMCU 600 wouldchange from 000000 to 100000, setting the bit that corresponds to row r1to “1”. In addition, a record is stored within SMU 604 indicating thebit for r1 was changed at T5.

At yet a later time T9, another update operation changes row r3.Specifically, the second update changes the value of r2 c 3 from A to B.In response to this update, the changed-row bitmap 606 of IMCU 600 wouldchange from 100000 to 101000, setting the bit that corresponds to row r3to “1”. In addition, a record is stored within SMU 604 indicating thatthe bit for row r3 was set at time T9.

After these updates have occurred, the database server may execute atransaction that reads the values of columns c1 and c3. If the snapshottime of the transaction is earlier than T5, then the transaction mayread all the values from column vector 220 and 222. The database maydetermine this by comparing the snapshot time of the transaction to thetimes indicated in the record of bit changes 608. If the snapshot timeof the transaction is after the IMCU version time, but before any timesin the record of bit changes 608, than all values in the IMCU 600 arevalid relative to that transaction.

If the snapshot time of the transaction is after T5 but before T9, thenthe transaction may read all values from column vectors 220 and 222except the values from row r1, which must be obtained elsewhere (e.g.from a journal or from the PF data). If the snapshot time of thetransaction is after T9, then the transaction may read all values fromcolumn vectors 220 and 222 except the values from rows r1 and r3, whichmust be obtained elsewhere.

Delete Vectors

In one embodiment, to account for the snapshot time of transactions thatread values that are mirrored in IMCU 600, the changed-row bitmap 606 isused in conjunction of the record of bit changes 608 to create a deletevector for each transaction that seeks to read data from IMCU 600. Adelete vector is snapshot-time specific, because bits in the deletevector are only set for rows that were modified before the snapshot-timeassociated with the transaction for which the delete vector isconstructed. Stated another way, each delete vector reflects the versionof the changed-row bitmap that was current as of the snapshot-time.Thus, the older the snapshot-time associated with a delete vector, theolder the version of the changed-row bitmap the delete vector reflects,and thus fewer the number of bits that will be set in the delete vector.

For a transaction with a snapshot time after the version time of theIMCU, a delete vector is made for the transaction by “rolling back”changes that occurred to the changed-row bitmap 606 after thesnapshot-time of the transaction. For example, if a transaction has asnapshot time of T5, the database server searches the record of bitchanges 608 to identify changes that occurred after time T5. A copy ofthe changed-row bitmap 606 is made, and within that copy the bits thatcorrespond to the changes that occurred after time T5 are reset to “0”.For transactions with snapshot times before the version time of theIMCU, the delete vector may be generated by making a copy of thechanged-row bitmap 606, and within that copy setting to “1” the bits ofrows that were changed between the snapshot time of the query and theversion time of the IMCU.

Because delete vectors are transaction-specific, at any given time, anynumber of distinct transactions may be performing scans of the rows thatmap to a particular IMCU. Each of those transactions may have beenassigned a different snapshot-time. Consequently, each of thosetransactions will have a different delete vector, though all of thosedelete vectors are generated based on the same changed-row bitmap of theSMU that corresponds to the IMCU.

Post-Merge Retention of Pre-Merge Changed-Row Bitmaps

As mentioned above, when changes are merged into an IMCU, all values inthe changed-row bitmap of the IMCU are reset to “0” to indicate that norows have been changed since the new version time of the IMCU (whichwill be the time at which the IMCU is refreshed/merged). However, ratherthan simply discard or overwrite the existing changed-row bitmap, a copyof pre-merge changed-row bitmap may be saved. A saved copy of apre-merge changed-row bitmap is referred to herein as a “retainedbitmap”. As shall be described in greater detail hereafter, suchretained bitmaps allow a post-merge IMCU to be used to provide dataitems to transactions that have snapshot times before the merge.

For example, assume that an IMCU is constructed at time T1. From time T1to time T10, the changes made to the data items in the IMCU are recordedin its global journal, rather than being made directly to the data itemsthemselves within the IMCU. While those changes are being recordedwithin the journal, the changes are also causing corresponding bits tobe set in the changed-row bitmap of the IMCU. At time T10, the changesare merged into the IMCU, causing the version time of the IMCU to changefrom T1 to T10.

Under these circumstances, the state of the changed-row bitmapimmediately before the merger reflects which rows, within the IMCU hadchanged between time T1 and time T10. By indicating which rows hadchanged between time T1 and time T10, the changed-row bitmap likewiseindicates which rows had not changed between time T1 and time T10.Within the post-merger IMCU, those rows that had not changed betweentime T1 and time T10 can be provided to transactions that have snapshottimes between T1 and T10.

Specifically, a copy of the pre-merge version of the changed-row bitmapis retained after the merger. Along with the retained bitmap, theversion timestamp of the pre-merge IMCU is also stored. In the examplegiven above, the retained bitmap would be associated with the versiontimestamp of T1.

When a transaction (a) requires data items that map to an IMCU, and (b)has a snapshot time that falls between the retrained bitmap time and thecurrent IMCU time, the retained bitmap is used to identify the rows thatwere not changed between the retained bitmap time and the current IMCUtime. Values for the identified rows may be provided to the transactionfrom the current IMCU. Values for the remaining rows are obtainedelsewhere. Specifically, values for the remaining rows may be obtainedfrom the global journal of the IMCU if the relevant journal entries havenot yet been purged, or from the PF data.

IMCU Refresh Undo

Rather than store a single retained bitmap in response to the mostrecent merge, a separate retained bitmap may be stored in response toeach merge. The retained bitmaps for a given IMCU may be linked inchronological order. The linked set of retained bitmaps for an IMCUconstitutes an “IMCU refresh undo” for the IMCU.

For example, assume that an IMCU was created at time T1, and thenrefreshed/merged at times T10, T15 and T30. Under these circumstances,the IMCU refresh undo for the IMCU would contain three retained bitmapsRB1, RB2 and RB3. These three retrained bitmaps would be associated withtimes T1, T10 and T15, respectively.

In the present example, the “0” bits of RB1 indicate the rows that werenot changed between times T1 and T10. The “0” bits of RB2 indicate therows that were not changed between the times T10 and T15. The “0” bitsof RB3 indicate the rows that were not changed between the times T15 andT30.

Given any snapshot time, the IMCU refresh undo may be used to identifywhich rows, within the current IMCU can be provided to a transactionwith that snapshot time. For example, for a transaction with thesnapshot time T18, the “0” bits in RB3 would indicate which rows can beprovided to the transaction from the current IMCU. As another example,for a transaction with the snapshot time of T12, RB2 and RB3 can becombined using a logical OR operation to produce a bitmap that indicateswhich rows can be provided to the transaction from the current IMCU. Asyet another example, for a transaction with the snapshot time of T5,RB1, RB2 and RB3 can be combined using a logical OR operation to producea bitmap that indicates which rows can be provided to the transactionfrom the current IMCU.

Thus, given a transaction with the snapshot time of TX, the retainedbitmap with the highest timestamp that is below TX is combined, using alogical OR operation, with all more recent retained bitmaps of the sameIMCU. The logical “OR” operation produces a bitmap where the “0”scorrespond to rows that have not changed since TX and the version timeof the current IMCU. Consequently, data items for those rows may beprovided by the IMCU.

Transaction Downgrade Based on Memory Constraints

As mentioned above, changes made to items in an IMCU are recorded injournals rather than made directly to the items in the IMCU. Thejournals are maintained in volatile memory. Unfortunately, long-runningtransactions that make changes to large numbers of items may cause somany journal entries to be generated that there is insufficient room tostore the entries in volatile memory.

Under these circumstances, the journal entries may be flushed topersistent storage to free up space in volatile memory. However,flushing the journal entries to persistent storage, and reading theentries from persistent storage thereafter, incurs a significantperformance penalty. Therefore, according to one embodiment,transactions that are generating journal entries in sufficient quantityto cause memory issues are “downgraded”.

According to one embodiment, such transactions are downgraded by pushingtheir existing private journal entries to the global journal of theIMCU, and ceasing to generate further private journal entries. Althoughin the global journal of the IMCU, such journal entries are not visibleto other transactions because the journal entries are for an uncommittedtransaction, and therefore initially associated with an “indeterminate”timestamp. When the downgraded transaction commits, the timestamps ofthe transaction's entries in the global journal are changed fromindeterminate to the commit time of the transaction.

Rather than cease the generation of journal entries when in downgradedmode, transactions may continue to generate journal entries until thesize of their private journal once again reaches the specifiedthreshold. At that point, the private journal entries may once again bemoved to the global journal, where the entries will not be visible toother transaction due to their indeterminate timestamp. This process offilling the private journal to a threshold, and then moving the entriesto the global journal, may be repeated any number of times until thetransaction either commits or is rolled back.

Regardless of whether a transaction that is operating in the downgradedmode continues to generate further private journal entries to record itschanges, the changes are still recorded in the record of bit changesassociated with the IMCU. Once the transaction commits, those bitchanges are made to the changed-row bitmap.

By using the changed-row bitmap to record the fact that a changeoccurred, future transactions will avoid reading stale data items fromthe IMCU. When the changed-row bitmap indicates that data itemsassociated with a particular row are invalid, transactions that requiredata items from that row must obtain the data items for a source otherthan the IMCU. In the case that the changes were made by a downgradedtransaction that ceased generating journal entries, the changes will notappear in the global journal, so the data items are retrieved from thePF data.

In one embodiment, not all transactions that are using an IMCU aredowngraded at once. Rather, the downgrades are performed on aper-transaction basis, where transactions are only downgraded if theysatisfy certain criteria. The criteria may be, for example, that theamount of journal entries that they have generated exceeds a particularthreshold.

In general, transactions must see the uncommitted changes that they havemade themselves. Consequently, a downgraded transaction that has ceasedgenerating journal entries may have to obtain the values of some dataitems that the transaction previously changed from the PF data, since nojournal entry exists for those changes.

Maintaining Sync Without Journals

In the sections above, it is explained that the MF data may be kept insync with the PF data by recording changes in journals, while leavingthe compressed MF data intact until the journals are merged into thecompressed MF data. However, in an alternative embodiment, for one ormore of the IMCUs, the MF data may be maintained in sync merely byinvalidating the data in response to changes made to the correspondingPF data without using journals to record the changes.

In such an embodiment, delete vectors may be generated for atransaction, as described above. For those bits that are not set, thedata may be obtained from the appropriate IMCU. For those bits that areset, the data must be retrieved from the PF data, since obtaining datafrom in-memory journals is not an option when no such journals aremaintained.

The benefit of invalidating the MF data without recording the changes injournals is that the processing overhead and memory consumption ofmaintaining the journals is avoided. However, when data items in theIMCU are too stale to be used to process a transaction, accessing theappropriate version of the data items from the PF data will generallyincur more overhead than would be required to obtain the data items fromjournals. In addition, refreshing the IMCUs in the absence of in-memoryjournals will also typically incur more overhead, because the changesthat need to be merged into the IMCUs must be obtained from the PF datarather than from in-memory journals.

In some embodiments, journals may be maintained for some IMCUs, but notothers. In addition, it is possible for the journal of an IMCU to bedropped, and yet continue to use the IMCU for data that has not beeninvalidated due to changes between the IMCU version time and thesnapshot time of the transactions that require the data.

Determining From Where to Obtain Data

Because MF data 104 is merely a mirror of some of the PF data (albeit ina different format), all data items contained in MF data 104 are also inthe PF data. Therefore, for any query that requires access to data itemsthat are mirrored in the MF data, the database server has the choice ofobtaining that data from MF data 104, from the PF data, or partiallyfrom the MF data 104 and partially from the PF data.

In general, when the requested data is an entire row of a table (or themajority of columns of the table), the location from which to mostefficiently retrieve the data is cache 106 (assuming that the persistentformat is row-major). If the requested row does not currently reside incache 106, but the MF data 104 has all columns of the row, then MF data104 is the location from which to most efficiently retrieve the row.Assuming that MF data 104 is column-major, MF data 104 is less efficientthan cache 106 for retrieving a row because, in column-major format, thevalues for the row must be pieced together from various places withinthe MF data 104.

If not all of the data for the requested row is in the MF data 104, thenat least some of the row must be retrieved from persistent storage 110.Typically, persistent storage 110 is the least efficient location fromwhich to retrieve data, because disk accesses are significantly slowerthan operations on data stored in volatile memory.

According to one embodiment, the decision of where to get the data maybe made at any of numerous levels of granularity. For example, thedecision of from where to obtain the data may be made on a per-tablebasis, a per-column basis, a per extent basis, a per segment basis, aper-table-partition basis, etc. Thus, even though all data from columnc1 is in column vector 220, the database server may decide to execute ascan by obtaining some of the values of column c1 from column vector220, and by obtaining the rest of the values of column c1 from the PFdata on persistent storage 110.

According to one embodiment, database objects, such as tables, can be“in-memory enabled”. A table that has been in-memory enabled has atleast a portion of its data mirrored in the MF data. For example, table200 is in-memory enabled because data from two of its columns (c1 andc3) are mirrored in mirror format data 104. Specifically, data fromcolumn c1 of table 200 is mirrored in column vector 220, and data fromcolumn c3 of table 200 is mirrored in column vector 222.

When a table is not mirror-enabled, a scan of the table is performed byreading PF data from cache 106 and/or from persistent storage 110. Onthe other hand, when a table is mirror-enabled, it may be also possibleto get some or all of the table's data from MF data 104. Morespecifically, it may be possible to obtain data of a mirror-enabledtable from any of the following locations:

-   -   the persistently-stored PF data    -   from a flash cache (described in U.S. patent application Ser.        No. 13/840,811, filed Mar. 15, 2013, the entire contents of        which is incorporated herein by reference)    -   the locally cached PF data    -   the PF data in the cache of another instance    -   the locally stored MF data    -   the MF data stored in the volatile memory of another instance    -   the locally stored MF data updated with information from        journals    -   entirely from the journals    -   MF data stored in the volatile memory of another instance        updated with information from journals    -   any combination of the above.

Further, the data can be obtained without the use of any indexes, withthe use of conventional indexes on the PF data, and/or with the use ofin-memory indexes. Further, indexes need not be used in conjunction onlywith the format based upon which the indexes were built. Thus,conventional indexes built on the PF data may be used to identify rowsthat must be retrieved, and then data for those rows may be retrievedfrom the MF data. Similarly, an in-memory index may be used to identifyrows that must be retrieved, and some or all of those rows may beretrieved from the PF data.

According to one embodiment, a cost-based optimizer is used todetermine, for any given database operation, which of the sources (orwhich combination of these sources) will be used to supply the dataneeded by the database operation. Additional factors used by thecost-based optimizer include whether conventional and/or in-memoryindexes exist for quickly locating the desired data.

Scanning Operations

According to one embodiment, when it is determined that a table scanoperation is to obtain at least some of the requested data from MF data104, a determination is made as to whether the timestamp associated withthe MF data 104 is earlier than the snapshot timestamp being used by thescan. In embodiments where the MF data 104 is contained in an IMCU, thedetermination is made by comparing the IMCU version time, stored in theSMU of the IMCU, to the snapshot time of the transaction associated withthe table scan.

If the MF data timestamp is earlier than the snapshot timestamp beingused by the scan, then is possible that some of the data in the IMCU isstale relative to that snapshot time. Under these circumstances, it ispossible that the required versions of data items that are stale in theIMCU reside in the global journal of the IMCU or the private journal ofthe transaction. In this case, the journals associated with the IMCU mayalso be scanned to obtain the correct version of the data that is stalein the IMCU.

Referring to FIG. 6, assume that column vector 220 has the currentversion of all values from column c1 of table 200 as of time T1.However, at time T3, R3C1 was changed from X to Y. For R3C1, columnvector 220 has the old value X, while journal 602 has the new value Y.Thus, when a table scan with a snapshot time of T5 uses IMCU 600 as asource for any of its data, both the compressed MF data in IMCU 600 andthe global journal 602 of IMCU 600 are scanned.

In addition to scanning the global journal 602, the private journal ofthe transaction that is performing the scan is also scanned. Forexample, if the transaction performing the scan is TX1, then privatejournal 662 is also scanned.

Thus, any given table scan may involve scanning the compressed MF datain IMCU 600, scanning global and private journals (e.g. journals 602 and662), and scanning the PF data (some of which may be in cache 106). Eachof these scans can be performed independently and in parallel. Thus, inresponse to a query that requests values from columns c1 and c2 of table200, the database server may, in parallel, (a) scan column vector 220for values from c1, (b) scan journal 602 for updated values from c1, (c)scan journal 662 for updated values of c1, and (d) scan the PF datastructures 108 to obtain the values for c2 of table 200.

Scanning Operation Example

Referring to FIG. 7, it is a block diagram of the steps performed by adatabase server in response to a request to scan a table. The table thatis being scanned is split into segments, where each segment includes aset of extents, and each extent includes a set of blocks. In thiscontext, database server determines which blocks contain data that needsto be scanned, and whether to scan the blocks from the PF data, or toobtain the data from the MF data.

Specifically, at step 700, the database server determines where the scanoperation is “memory-enabled”. An operation is “memory-enabled” if theoperation is permitted to obtain some or all of the data it requiresfrom the MF data. The scan operation may automatically be treated asmemory-enabled, for example, if the table that is being scanned (the“target table”) is designated as memory-enabled. A table is“memory-enabled” if data from the table is to be mirrored in the MFdata. As described elsewhere, data items from a memory-enabled tablemaybe proactively loaded into IMCUs, or may be loaded into IMCUs on anon-demand basis. Even if the target table is designated asmemory-enabled, a switch may be provided to designate the scan operationas either memory-enabled or not-memory-enabled. A scan operation may bedesignated as not-memory-enabled to force the scan to be executedagainst only the PF data.

According to one embodiment, the memory-enabled designation may be madeat any of a number of levels of granularity. For example, thedesignation may be made on a per-table basis, a per-partition basis, aper-segment basis, or a per-extent basis. For the purpose ofillustration, it shall be assumed that the memory-enabled designation ismade at the per-extent basis.

Referring again to FIG. 7, if the scan is not memory-enabled, thencontrol passes to step 780 and the scan is performed only against the PFdata. After the PF data has been used to perform the scan, the scanoperation is done (step 782).

On the other hand, if the scan operation is memory-enabled, then controlproceeds to step 702. At step 702, the database server determines therange of blocks that contain data required by the scan. Once the rangehas been determined, control passes to step 704. For the purpose ofillustration, it shall be assumed that blocks B1 to B500 contain thedata required by the scan operation.

Step 704 is the beginning of a loop that iterates through each block inthe range identified in step 704. If at step 704 it is determined thatthere are no more blocks to scan, then control passes to step 782 andthe scan operation is done. If some blocks have not yet been scanned,than control passes from step 704 to step 706.

At step 706, the database server determines the next block, from therange identified in step 702, to scan. At step 708, it is determinedwhether the address of the block selected in step 706 maps to an IMCU.If the address maps to an IMCU, then the IMCU stores MF versions of atleast some data items from the segment. If the IMCU stores MF versionsof data items from the segment, then control passes to step 710.Otherwise, control passes to step 712, where the segment that includesthe block is obtained from the PF data.

In one embodiment, upon obtaining the PF version of a segment that ismapped to an IMCU, the database server converts the segment into thein-memory format, and stores the MF data thus produced in the IMCU. Suchan embodiment employs on-demand loading, which is described in greaterdetail hereafter. The conversion and loading of the data into the IMCUmay take some time. Therefore, at step 714, the database serverdetermines whether to wait for the data from the segment to be convertedand loaded. If the database determines to wait, then the database serverwaits, and control passes to step 708 when the data from the segment hasbeen converted and loaded into the IMCU. If the database serverdetermines not to wait, the data items are obtained from the PF data(step 720), and control returns to step 704.

As mentioned above, control passes to step 710 when it is determinedthat the address of the block maps to an IMCU. When the address of theblock maps to an IMCU, the IMCU contains an MF version of at least someof the data items in the block. However, the versions of the data itemsthat are contained in the IMCU are not necessarily valid relative to thesnapshot time of the scan. Therefore, at step 710, it is determinedwhether the version of those data items in the IMCU is valid for thetransaction that is executing the scan. In one embodiment, determiningwhether the data in the IMCU is valid involves generating a deletevector for the scan operation based on the snapshot time associated withthe scan operation, the changed-row bitmap of the IMCU, and the recordof bit changes for the IMCU. As described above, the delete vector is asnapshot-specific bitmap where each set bit indicates that the rowcorresponding to the bit is invalid relative to the snapshot time.

If, at step 710, it is determined that no data items for the currentblock are valid in the IMCU, control passes to step 716 where data itemsare obtained from the PF data until the end of the current extent. Thencontrol passes back to step 704. In some situations, even though no dataitems for the current block are valid in the IMCU, the database servermay not immediately attempt to obtain the PF data from disk. Instead,the database server may attempt to retrieve the PF data only afterreaching the end of the current extent.

If the IMCU has valid versions for at least some of the items, thencontrol passes to step 722. At step 722, the data items for which theIMCU has valid versions are fetched from the IMCU. The data items forwhich the IMCU does not have valid versions are fetched either fromentries in the global journal of the IMCU, or from the PF data. Asexplained elsewhere, various factors may affect the selection of thesource from which to obtain data items. Such factors may include, forexample, whether the PF disk block that stores the correct version ofthe data items currently resides in cache. It is possible that only asubset of the data in a segment is mapped to an IMCU. For example, itmay be that only a subset of a table's columns are mapped to an IMCU.Under these circumstances, any data items in the segment that arerequired by the scan but not mapped to the IMCU must be obtained fromthe PF data.

If the private journal of the transaction performing the scan hasupdated versions of any of the data obtained from the IMCU or the globaljournal, those updated versions are provided in place of any versionotherwise obtained. This ensures that the scanning transaction sees itsown changes, even though those changes have not been committed.

Even when the delete vector indicates that the IMCU has valid data forall rows, the global journal is checked to identify rows that wereinserted after the IMCU was created. If the journal does not contain theactual data items for those rows, then the rows are retrieved from thePF data. Similarly, the private journal of the transaction is checkedfor rows newly inserted by the transaction, and for data items that havebeen changed by the transaction.

After fetching all necessary data items, control passes from step 722back to step 704. At step 704, the loop is repeated until data itemsrequired by the scan have been obtained, either from an IMCU, fromjournal entries, or from the PF data.

When to Create the MF Data

Before MF data may be used to satisfy a query, or to improve performanceof a query whose results are ultimately obtained from the PF data, theMF data must be present in volatile memory. Unlike cache 106, mirrorformat data is not simply a copy of the data that is stored onpersistent storage 110. Rather, because the mirror format is not basedon the persistent format, volatile memory 102 is initially populated by(a) reading the PF data from persistent storage 110 and (b) convertingthe PF data thus obtained to the MF format.

The amount of overhead that is required to perform the PF-to-MFconversion will vary from situation to situation, based on how differentthe mirror format is from the persistent format. For example, if thepersistent format is row-major disk blocks that have been compressed oneway, and the mirror format is column vectors that are compressed anotherway, the amount of overhead required to perform the conversion may beextensive.

The decision about when to create the MF data may be based on a varietyof factors. For example, if sufficient time is available at systemstart-up, all of the PF data that has been selected for mirroring may bepre-loaded into volatile memory 102 on start up. As mentioned above,loading the MF data involves reading the corresponding PF data frompersistent storage 110 and then converting that PF data into the mirrorformat.

Pre-Loading the MF Data

In one embodiment, the MF data is pre-loaded into volatile memory atdatabase system start up. The pre-loading may be performed, for example,by background processes before any database operation is executedagainst the memory-enabled data structures that contain the data itemsthat will be mirrored by the MF data.

The MF data may be created one-IMCU at a time. In multi-instanceenvironment, durably stored metadata may be used to determine which MFdata is pre-loaded into which database instance. Such metadata mayinclude, for example, a MF-data-to-IMCU mapping and an IMCU-to-instancemapping.

In a simple example, the MF-data-to-IMCU mapping may indicate that IMCU402 is to store the column vector 220 for c1, and that IMCU 404 is tostore the column vector 222 of column c3. The IMCU-to-instance mappingmay indicate that IMCU 402 is to be loaded into the volatile memory 502of instance 1, while IMCU 404 is to be loaded into the volatile memory504 of instance 2. Based on these mappings, the MF data would bepre-loaded into volatile memory in the manner illustrated in FIG. 5 b.

On-Demand Loading of MF Data

Rather than simply pre-load the MF data, some or all of the MF data maybe generated at the time that the corresponding PF data is accessed by adatabase operation. For example, assume that database instance 1 isassigned to host the column vectors for columns c1 and c3 of table 200.Rather than construct and load those column vectors on start-up,database instance 1 may initially generate no MF data. Instead, thedatabase instance 1 may wait until a database command requires a scan oftable 200. Because no MF data has been created yet, the scan isperformed based entirely on the PF data. During that scan, the valuesneeded to construct the column vectors for c1 and c2 will be accessed.Therefore, the column vectors for c1 and c2 may be built at that timewithout incurring any additional disk accesses.

On-demand loading of MF data may be used in conjunction withpre-loading. For example, some of the MF data that is to be hosted oninstance 1 may be created at the time instance 1 is started. Otherportions of the MF data may be constructed at the time the data isaccessed by queries.

In one embodiment, users may set configuration options to indicate whichMF data to pre-load, and which MF data to load on-demand. In analternative embodiment, the database server automatically determineswhich portions of the MF data are pre-loaded and which are loadedon-demand. In general, the more frequently a data item is used, the morelikely the database server will automatically pre-load the data iteminto MF data so that even the first database operation that requires thedata item has the option of obtaining the data from the MF data.

Persistent Storage of IMCU Images

As mentioned above, the MF data may be created on start-up, on-demand,or any combination thereof. In one embodiment, images of IMCUs may beperiodically stored to disk. Such persistently-stored images may be usedto re-populate volatile memory 102 with MF data after a crash. The imageof any given IMCU will be current as of a “checkpoint time”, which maybe when the IMCU image was persistently stored. However, that checkpointtime may be before the time that the crash occurred. Consequently,between the checkpoint time of an IMCU image and the time of the crash,additional changes may have been made to the IMCU. Since those changesare not reflected in the stored image, the IMCU image may be stale.

To use an otherwise stale IMCU image, the IMCU image may first be loadedinto volatile memory. The IMCU data thus loaded may be usable, inconjunction with durably stored undo information, for database commandsthat have snapshot times before the checkpoint time associated with theIMCU image. To be usable with database commands that have snapshot timesafter the checkpoint time, redo information that was durably stored forthe associated PF data prior to the crash may be used to populate thestale journals of the IMCU image with journal entries for the changesthat occurred after the checkpoint time of the IMCU.

Depending on how many changes were made after the checkpoint time andbefore the crash, reconstructing an IMCU using a stale persistentlystored image of the IMCU may consume significantly less overhead thancompletely re-generating the IMCU data from the PF data.

Selecting Which PF Data to Mirror

The decision of which PF data to mirror, and when to load it, may bebased on a variety of factors. For example, if a system has an enormousabout of volatile memory 102, and a relatively small database, it may bedesirable to mirror the entire database. Thus, all PF data would also bemirrored in the MF data. On the other hand, if there is a relativelysmall amount of volatile memory 102 relative to the size of thedatabase, then it may be optimal to only mirror a very small fraction ofthe database.

Typically, when not all of the database is to be mirrored, the portionthat is selected to be mirrored is based on which portion will mostimprove overall performance of the system. Typically, mirroring datathat is used frequently will provide more benefit than mirroring datathat is used less frequently. Thus, if one table, one column of a table,or one partition of a table is access more frequently than other data inthe database, that table, column or partition may be selected to bemirrored in volatile memory 102. The selection of which portions of adatabase to mirror may be made at any level of granularity. For example,the selection may be made on a per-table basis, a per-column basis, aper extent basis, a per segment basis, a per-table-partition basis, etc.

Self-Verification

In systems that maintain MF data in addition to the PF data, multiplesources of the same data are available to process some queries. In theforegoing sections, it has been explained that when multiple sources ofthe same data are available, a database server may select from among thepossible sources based on which source will result in the most efficientprocessing of the requested database operation.

However, rather than select one of the possible sources, a databaseserver may alternatively execute the database operation, in parallel,against each of the two or more sources. For example, a query thatselects data from column c1 of table 200 may be answered with MF datafrom column vector 220, or with PF data from PF data structures 108.Rather than select one or the other, the database server can execute theoperation, separately and independently, against both sources. Oncefinished, the results produced by the various sources may be comparedagainst each other. If the result sets do not match, then an erroroccurred during the processing of at least one of the operations.

The database server may take any number of possible actions when such anerror is detected. For example, in one embodiment, an alert is generatedto indicate the occurrence of the error. The alert may indicate what thediscrepancy is between the two results sets. Instead of or in additionto generating an alert, the database server may perform additionaldebugging operations, including but not limited to re-executing theoperation turning off or on different database features to determine thefeature whose use produces the error.

When the results sets match, the user may have a greater degree ofconfidence that the results of the operation are accurate. Thus,parallel execution, by the same database instance, of the same operationagainst multiple sources of the same data (the MF data and the PF data)provides an on-the-fly “double check” to verify the result set of theoperation.

Typically, the execution of the database operations against the twosources may be done in parallel, so that performing self-verificationhas little performance impact on the operation relative to performingthe operation only on the PF data. According to one embodiment,self-verification may be enabled at a highly granular level. Forexample, self-verification may be enabled on a per-session basis. Thus,the additional overhead incurred by self-verification may be incurred inonly those sessions a user wishes to “test” for accuracy.

Self-verification operations may also be initiated by the system itself.For example, rather than receive a request from an application toexecute a database command, the database system may be configured toidentify and select “critical” database commands from those that havealready been executed by the database system. During periods of low use,the database server may execute one or more of those selected databasecommands in the background. The selected database commands are executedin self-verification mode to concurrently produce multiple copies of theresult set, one based on the MF data and one based on the PF data. Theresult sets are compared to ensure that the result sets are identical.If not identical, an error message may be sent to a user and/or recordedin a log. If identical, data may be stored to indicate that the selecteddatabase command passed a self-verification test. After passing athreshold number of tests (where the threshold may be 1), the databaseserver may be configured to cease to select the database command forautomated background self-verification.

In one embodiment, rather than simply generate an alert when aself-verification test fails, the database command is repeatedlyretested under different conditions. To ensure that the repeats of theoperation are as similar as possible to the original operation thatproduced the self-verification error, the same database operation may beexecuted with the same snapshot time as was used during the session thatencountered the error.

In many database systems, numerous advanced query processing featuresmay have virtual “on-off” switches, where the default state is “on”.During the repeats of a previously-failed self-verification test, thosefeatures may be selectively turned on and off. If the self-verificationpasses when a particular feature is turned off, and fails when the sameparticular is turned on, then there is a likelihood that the error isrelated to that feature.

Having determined that use of a particular feature causes aself-verification problem with a particular database operation, aquarantine may be enforced. The scope of the quarantine may vary. Forexample, the database server may automatically turn off the particularfeature for all future database commands, for all future databasecommands that target the same data as the database operation thatencountered the error, or for only future executions of the specificdatabase command that encountered the error.

Hardware Overview

According to one embodiment, the techniques described herein areimplemented by one or more special-purpose computing devices. Thespecial-purpose computing devices may be hard-wired to perform thetechniques, or may include digital electronic devices such as one ormore application-specific integrated circuits (ASICs) or fieldprogrammable gate arrays (FPGAs) that are persistently programmed toperform the techniques, or may include one or more general purposehardware processors programmed to perform the techniques pursuant toprogram instructions in firmware, memory, other storage, or acombination. Such special-purpose computing devices may also combinecustom hard-wired logic, ASICs, or FPGAs with custom programming toaccomplish the techniques. The special-purpose computing devices may bedesktop computer systems, portable computer systems, handheld devices,networking devices or any other device that incorporates hard-wiredand/or program logic to implement the techniques.

For example, FIG. 9 is a block diagram that illustrates a computersystem 900 upon which an embodiment of the invention may be implemented.Computer system 900 includes a bus 902 or other communication mechanismfor communicating information, and a hardware processor 904 coupled withbus 902 for processing information. Hardware processor 904 may be, forexample, a general purpose microprocessor.

Computer system 900 also includes a main memory 906, such as a randomaccess memory (RAM) or other dynamic storage device, coupled to bus 902for storing information and instructions to be executed by processor904. Main memory 906 also may be used for storing temporary variables orother intermediate information during execution of instructions to beexecuted by processor 904. Such instructions, when stored innon-transitory storage media accessible to processor 904, rendercomputer system 900 into a special-purpose machine that is customized toperform the operations specified in the instructions.

Computer system 900 further includes a read only memory (ROM) 908 orother static storage device coupled to bus 902 for storing staticinformation and instructions for processor 904. A storage device 910,such as a magnetic disk, optical disk, or solid-state drive is providedand coupled to bus 902 for storing information and instructions.

Computer system 900 may be coupled via bus 902 to a display 912, such asa cathode ray tube (CRT), for displaying information to a computer user.An input device 914, including alphanumeric and other keys, is coupledto bus 902 for communicating information and command selections toprocessor 904. Another type of user input device is cursor control 916,such as a mouse, a trackball, or cursor direction keys for communicatingdirection information and command selections to processor 904 and forcontrolling cursor movement on display 912. This input device typicallyhas two degrees of freedom in two axes, a first axis (e.g., x) and asecond axis (e.g., y), that allows the device to specify positions in aplane.

Computer system 900 may implement the techniques described herein usingcustomized hard-wired logic, one or more ASICs or FPGAs, firmware and/orprogram logic which in combination with the computer system causes orprograms computer system 900 to be a special-purpose machine. Accordingto one embodiment, the techniques herein are performed by computersystem 900 in response to processor 904 executing one or more sequencesof one or more instructions contained in main memory 906. Suchinstructions may be read into main memory 906 from another storagemedium, such as storage device 910. Execution of the sequences ofinstructions contained in main memory 906 causes processor 904 toperform the process steps described herein. In alternative embodiments,hard-wired circuitry may be used in place of or in combination withsoftware instructions.

The term “storage media” as used herein refers to any non-transitorymedia that store data and/or instructions that cause a machine tooperate in a specific fashion. Such storage media may comprisenon-volatile media and/or volatile media. Non-volatile media includes,for example, optical disks, magnetic disks, or solid-state drives, suchas storage device 910. Volatile media includes dynamic memory, such asmain memory 906. Common forms of storage media include, for example, afloppy disk, a flexible disk, hard disk, solid-state drive, magnetictape, or any other magnetic data storage medium, a CD-ROM, any otheroptical data storage medium, any physical medium with patterns of holes,a RAM, a PROM, and EPROM, a FLASH-EPROM, NVRAM, any other memory chip orcartridge.

Storage media is distinct from but may be used in conjunction withtransmission media. Transmission media participates in transferringinformation between storage media. For example, transmission mediaincludes coaxial cables, copper wire and fiber optics, including thewires that comprise bus 902. Transmission media can also take the formof acoustic or light waves, such as those generated during radio-waveand infra-red data communications.

Various forms of media may be involved in carrying one or more sequencesof one or more instructions to processor 904 for execution. For example,the instructions may initially be carried on a magnetic disk orsolid-state drive of a remote computer. The remote computer can load theinstructions into its dynamic memory and send the instructions over atelephone line using a modem. A modem local to computer system 900 canreceive the data on the telephone line and use an infra-red transmitterto convert the data to an infra-red signal. An infra-red detector canreceive the data carried in the infra-red signal and appropriatecircuitry can place the data on bus 902. Bus 902 carries the data tomain memory 906, from which processor 904 retrieves and executes theinstructions. The instructions received by main memory 906 mayoptionally be stored on storage device 910 either before or afterexecution by processor 904.

Computer system 900 also includes a communication interface 918 coupledto bus 902. Communication interface 918 provides a two-way datacommunication coupling to a network link 920 that is connected to alocal network 922. For example, communication interface 918 may be anintegrated services digital network (ISDN) card, cable modem, satellitemodem, or a modem to provide a data communication connection to acorresponding type of telephone line. As another example, communicationinterface 918 may be a local area network (LAN) card to provide a datacommunication connection to a compatible LAN. Wireless links may also beimplemented. In any such implementation, communication interface 918sends and receives electrical, electromagnetic or optical signals thatcarry digital data streams representing various types of information.

Network link 920 typically provides data communication through one ormore networks to other data devices. For example, network link 920 mayprovide a connection through local network 922 to a host computer 924 orto data equipment operated by an Internet Service Provider (ISP) 926.ISP 926 in turn provides data communication services through the worldwide packet data communication network now commonly referred to as the“Internet” 928. Local network 922 and Internet 928 both use electrical,electromagnetic or optical signals that carry digital data streams. Thesignals through the various networks and the signals on network link 920and through communication interface 918, which carry the digital data toand from computer system 900, are example forms of transmission media.

Computer system 900 can send messages and receive data, includingprogram code, through the network(s), network link 920 and communicationinterface 918. In the Internet example, a server 930 might transmit arequested code for an application program through Internet 928, ISP 926,local network 922 and communication interface 918.

The received code may be executed by processor 904 as it is received,and/or stored in storage device 910, or other non-volatile storage forlater execution.

In the foregoing specification, embodiments of the invention have beendescribed with reference to numerous specific details that may vary fromimplementation to implementation. The specification and drawings are,accordingly, to be regarded in an illustrative rather than a restrictivesense. The sole and exclusive indicator of the scope of the invention,and what is intended by the applicants to be the scope of the invention,is the literal and equivalent scope of the set of claims that issue fromthis application, in the specific form in which such claims issue,including any subsequent correction.

What is claimed is:
 1. A method for improving database commandprocessing in database systems, the method comprising: maintaining, by adatabase server, at least two copies of each item in a particular set ofdata; wherein, for each item in the particular set of data, the at leasttwo copies include: a first copy, stored on persistent storage, in apersistent format, and a second copy, stored in volatile memory, in amirror format; wherein the persistent format is different than themirror format; wherein the particular set of data includes a first itemand a second item; receiving, by the database server, a databasecommand; in response to the database command, the database serverexecuting a particular transaction to perform operations required by thedatabase command; when the particular transaction performs a firstoperation that makes a change to the first item, the database server:making the change to a copy of the first item that is in the persistentformat; storing, in an in-memory journal, an entry that indicates thechange without updating any copy of the first item that is in the mirrorformat, thereby allowing any copy of the first item that is in mirrorformat to go stale; and storing data that indicates which data items inthe mirror format have become stale to avoid reading stale data items inthe mirror format; after the particular transaction performs the change,when the particular transaction performs a second operation thatinvolves the first item and the second item, the database server:obtaining a copy of the first item, which is a current version of thefirst item, based on the entry in the in-memory journal; and obtaining acopy of the second item, which is a current version of the second item,from volatile memory in the mirror format.
 2. The method of claim 1,wherein: the in-memory journal is a private journal; and the methodfurther comprises, upon commit of the particular transaction, moving oneor more entries, from the in-memory journal, to a global journal.
 3. Themethod of claim 2, further comprising: after commit of the particulartransaction, a second transaction obtaining a copy of the first item,that is the current version of the first item, based on a particularentry in the global journal; wherein the second transaction is distinctfrom the particular transaction.
 4. The method of claim 1, wherein: thesecond operation involves a third item; and the method furthercomprises, when the particular transaction performs the second operationthat involves the first item and the second item, the database serverobtaining a copy of the third item, which is a current version of thethird item, from persistent format data.
 5. The method of claim 4,wherein obtaining the copy of the third item from persistent format datacomprises obtaining a cached copy of the third item from volatilememory.
 6. The method of claim 1, wherein making the change to the copyof the first item that is in the persistent format comprises making thechange to a cached copy of the first item stored in volatile memory. 7.A method for improving change journaling in database systems, the methodcomprising: maintaining, by a database server, at least two copies ofeach item in a particular set of data; wherein, for each item in theparticular set of data, the at least two copies include: a copy, storedon persistent storage, in a persistent format; and a copy, stored involatile memory, in a mirror format; wherein the persistent format isdifferent than the mirror format; maintaining an in-memory journal withparticular entries that reflect committed changes, wherein each changeof the committed changes: was made to a copy of an item, in theparticular set of data, in the persistent format; and was not made toany copy of the item, in the particular set of data, in the mirrorformat, thereby allowing any copy of the item, in the mirror format, tobecome stale; storing data that indicates which data items in the mirrorformat have become stale to avoid reading stale data items in the mirrorformat; purging a portion of the particular entries from the in-memoryjournal; wherein the portion of the particular entries includesinformation for one or more changes to one or more items of theparticular set of data; wherein purging the portion of the particularentries from the in-memory journal is performed without having appliedthe one or more changes to any copies, of the one or more items, in themirror format; after purging the portion of the particular entries fromthe in-memory journal, providing, in response to a query, a copy of aparticular item, of the particular set of data, in the mirror format. 8.The method of claim 7, wherein purging the portion of the particularentries from the in-memory journal is performed based, at least in part,on memory availability within the volatile memory.
 9. The method ofclaim 7 wherein: the mirror format copies of items, in the particularset of data, belong to a set of mirror format data; the set of mirrorformat data reflects values that were current as of a particular time;the portion of the particular entries includes entries for changescommitted between a first time and the particular time; the first timeprecedes the particular time; and the method further comprises: beforepurging the portion of the particular entries from the in-memoryjournal, using the in-memory journal in conjunction with the set ofmirror format data as a source for data items for transactions withsnapshot times between the first time and the particular time; and afterpurging the portion of the particular entries from the in-memoryjournal, using persistent format data, that includes persistent formatcopies of items in the particular set of data, as a source for dataitems for transactions with snapshot times between the first time andthe particular time.
 10. The method of claim 7 wherein: the mirrorformat copies of items, in the particular set of data, belong to a setof mirror format data; the set of mirror format data reflects valuesthat were current as of a particular time; the portion of the particularentries includes entries for changes committed between a first time anda second time; the particular time precedes both the first time and thesecond time; and the first time precedes the second time; the methodfurther comprises: before purging the portion of the particular entriesfrom the in-memory journal, using the in-memory journal in conjunctionwith the set of mirror format data as a source for data items fortransactions with snapshot times between the particular time and thesecond time; and after purging the portion of the particular entriesfrom the in-memory journal: using the in-memory journal in conjunctionwith the set of mirror format data as a source for data items fortransactions with snapshot times between the particular time and thefirst time, and using persistent format data, that includes persistentformat copies of items in the particular set of data, as a source fordata items for transactions with snapshot times between the first timeand the second time.
 11. The method of claim 10 further comprising afterpurging the portion of the particular entries from the in-memoryjournal: identifying, based on a delete vector for a particulartransaction with a snapshot time of a third time, one or more particularitems that have not changed between the first time and the third time;wherein the third time is before the second time and the third time isafter the first time; in response to identifying the one or moreparticular items that have not changed between the first time and thethird time, using the in-memory journal in conjunction with the set ofmirror format data as a source, for the one or more particular items,for the particular transaction.
 12. One or more non-transitorycomputer-readable media storing one or more sequences of instructionswhich, when executed by one or more processors, cause: maintaining, by adatabase server, at least two copies of each item in a particular set ofdata; wherein, for each item in the particular set of data, the at leasttwo copies include: a first copy, stored on persistent storage, in apersistent format, and a second copy, stored in volatile memory, in amirror format; wherein the persistent format is different than themirror format; wherein the particular set of data includes a first itemand a second item; receiving, by the database server, a databasecommand; in response to the database command, the database serverexecuting a particular transaction to perform operations required by thedatabase command; when the particular transaction performs a firstoperation that makes a change to the first item, the database server:making the change to a copy of the first item that is in the persistentformat; storing, in an in-memory journal, an entry that indicates thechange without updating any copy of the first item that is in the mirrorformat, thereby allowing any copy of the first item that is in mirrorformat to go stale; and storing data that indicates which data items inthe mirror format have become stale to avoid reading stale data items inthe mirror format; after the particular transaction performs the change,when the particular transaction performs a second operation thatinvolves the first item and the second item, the database server:obtaining a copy of the first item, which is a current version of thefirst item, based on the entry in the in-memory journal; and obtaining acopy of the second item, which is a current version of the second item,from volatile memory in the mirror format.
 13. The one or morenon-transitory computer-readable media of claim 12, wherein: thein-memory journal is a private journal; and the one or more sequences ofinstructions further comprise instructions which, when executed by oneor more processors cause, upon commit of the particular transaction,moving one or more entries, from the in-memory journal, to a globaljournal.
 14. The one or more non-transitory computer-readable media ofclaim 13, wherein the one or more sequences of instructions furthercomprise instructions which, when executed by one or more processorscause: after commit of the particular transaction, a second transactionobtaining a copy of the first item, that is the current version of thefirst item, based on a particular entry in the global journal; whereinthe second transaction is distinct from the particular transaction. 15.The one or more non-transitory computer-readable media of claim 12,wherein: the second operation involves a third item; and the one or moresequences of instructions further comprise instructions which, whenexecuted by one or more processors cause, when the particulartransaction performs the second operation that involves the first itemand the second item, the database server obtaining a copy of the thirditem, which is a current version of the third item, from persistentformat data.
 16. The one or more non-transitory computer-readable mediaof claim 15, wherein obtaining the copy of the third item frompersistent format data comprises obtaining a cached copy of the thirditem from volatile memory.
 17. The one or more non-transitorycomputer-readable media of claim 12, wherein making the change to thecopy of the first item that is in the persistent format comprises makingthe change to a cached copy of the first item stored in volatile memory.18. One or more non-transitory computer-readable media storing one ormore sequences of instructions which, when executed by one or moreprocessors, cause: maintaining, by a database server, at least twocopies of each item in a particular set of data; wherein, for each itemin the particular set of data, the at least two copies include: a copy,stored on persistent storage, in a persistent format; and a copy, storedin volatile memory, in a mirror format; wherein the persistent format isdifferent than the mirror format; maintaining an in-memory journal withparticular entries that reflect committed changes, wherein each changeof the committed changes: was made to a copy of an item, in theparticular set of data, in the persistent format; and was not made toany copy of the item, in the particular set of data, in the mirrorformat, thereby allowing any copy of the item, in the mirror format, tobecome stale; storing data that indicates which data items in the mirrorformat have become stale to avoid reading stale data items in the mirrorformat; purging a portion of the particular entries from the in-memoryjournal; wherein the portion of the particular entries includesinformation for one or more changes to one or more items of theparticular set of data; wherein purging the portion of the particularentries from the in-memory journal is performed without having appliedthe one or more changes to any copies, of the one or more items, in themirror format; after purging the portion of the particular entries fromthe in-memory journal, providing, in response to a query, a copy of aparticular item, of the particular set of data, in the mirror format.19. The one or more non-transitory computer-readable media of claim 18,wherein purging the portion of the particular entries from the in-memoryjournal is performed based, at least in part, on memory availabilitywithin the volatile memory.
 20. The one or more non-transitorycomputer-readable media of claim 18 wherein: the mirror format copies ofitems, in the particular set of data, belong to a set of mirror formatdata; the set of mirror format data reflects values that were current asof a particular time; the portion of the particular entries includesentries for changes committed between a first time and the particulartime; the first time precedes the particular time; and the one or moresequences of instructions further comprise instructions which, whenexecuted by one or more processors cause: before purging the portion ofthe particular entries from the in-memory journal, using the in-memoryjournal in conjunction with the set of mirror format data as a sourcefor data items for transactions with snapshot times between the firsttime and the particular time; and after purging the portion of theparticular entries from the in-memory journal, using persistent formatdata, that includes persistent format copies of items in the particularset of data, as a source for data items for transactions with snapshottimes between the first time and the particular time.
 21. The one ormore non-transitory computer-readable media of claim 18 wherein: themirror format copies of items, in the particular set of data, belong toa set of mirror format data; the set of mirror format data reflectsvalues that were current as of a particular time; the portion of theparticular entries includes entries for changes committed between afirst time and a second time; the particular time precedes both thefirst time and the second time; and the first time precedes the secondtime; the one or more sequences of instructions further compriseinstructions which, when executed by one or more processors cause:before purging the portion of the particular entries from the in-memoryjournal, using the in-memory journal in conjunction with the set ofmirror format data as a source for data items for transactions withsnapshot times between the particular time and the second time; andafter purging the portion of the particular entries from the in-memoryjournal: using the in-memory journal in conjunction with the set ofmirror format data as a source for data items for transactions withsnapshot times between the particular time and the first time, and usingpersistent format data, that includes persistent format copies of itemsin the particular set of data, as a source for data items fortransactions with snapshot times between the first time and the secondtime.
 22. The one or more non-transitory computer-readable media ofclaim 21 wherein the one or more sequences of instructions furthercomprise instructions which, when executed by one or more processorscause, after purging the portion of the particular entries from thein-memory journal: identifying, based on a delete vector for aparticular transaction with a snapshot time of a third time, one or moreparticular items that have not changed between the first time and thethird time; wherein the third time is before the second time and thethird time is after the first time; in response to identifying the oneor more particular items that have not changed between the first timeand the third time, using the in-memory journal in conjunction with theset of mirror format data as a source, for the one or more particularitems, for the particular transaction.